{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "766a8fe8",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Python MySQL - mysql-connector 驱动\n",
    "MySQL 是最流行的关系型数据库管理系统\n",
    "本章节我们为大家介绍使用 mysql-connector 来连接使用 MySQL， mysql-connector 是 MySQL 官方提供的驱动器。\n",
    "我们可以使用 pip 或 conda(前提是你安装了Anaconda)命令来安装 mysql-connector：\n",
    "pip install mysql-connector-python\n",
    "或\n",
    "conda install mysql-connector-python\n",
    "使用以下代码测试 mysql-connector 是否安装成功：\n",
    "demo_mysql_test.py:\n",
    "import mysql.connector\n",
    "执行以上代码，如果没有产生错误，表明安装成功。\n",
    "\"\"\"\n",
    "\n",
    "import mysql.connector\n",
    "\n",
    "# 创建数据库连接\n",
    "# 可以使用以下代码来连接数据库：\n",
    "mydb = mysql.connector.connect(\n",
    "    host=\"localhost\",\n",
    "    username=\"root\",\n",
    "    password=\"\"\n",
    ")\n",
    "\n",
    "# 创建数据库\n",
    "# 创建数据库使用 \"CREATE DATABASE\" 语句，以下创建一个名为 db_test 的数据库：\n",
    "cursor = mydb.cursor()\n",
    "cursor.execute(\"create database db_test\")\n",
    "\n",
    "# 创建数据库前我们也可以使用 \"SHOW DATABASES\" 语句来查看数据库是否存在：\n",
    "#\n",
    "# demo_mysql_test.py:\n",
    "# 输出所有数据库列表：\n",
    "# import mysql.connector\n",
    "#\n",
    "# mydb = mysql.connector.connect(\n",
    "#   host=\"localhost\",\n",
    "#   user=\"root\",\n",
    "#   passwd=\"123456\"\n",
    "# )\n",
    "#\n",
    "# mycursor = mydb.cursor()\n",
    "#\n",
    "# mycursor.execute(\"SHOW DATABASES\")\n",
    "#\n",
    "# for x in mycursor:\n",
    "#   print(x)\n",
    "\n",
    "# 或者我们可以直接连接数据库，如果数据库不存在，会输出错误信息：\n",
    "#\n",
    "# demo_mysql_test.py:\n",
    "# import mysql.connector\n",
    "#\n",
    "# mydb = mysql.connector.connect(\n",
    "#   host=\"localhost\",\n",
    "#   user=\"root\",\n",
    "#   passwd=\"123456\",\n",
    "#   database=\"runoob_db\"\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ada9949",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "主键设置\n",
    "创建表的时候我们一般都会设置一个主键（PRIMARY KEY），我们可以使用 \"INT AUTO_INCREMENT PRIMARY KEY\"\n",
    "语句来创建一个主键，主键起始值为 1，逐步递增。\n",
    "如果我们的表已经创建，我们需要使用 ALTER TABLE 来给表添加主键：\n",
    "\"\"\"\n",
    "\n",
    "import mysql.connector\n",
    "\n",
    "mydb = mysql.connector.connect(host=\"localhost\", username=\"root\", password=\"\", database=\"db_test\")\n",
    "\n",
    "cursor = mydb.cursor()\n",
    "\n",
    "cursor.execute(\"ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e4cd9ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "创建数据表\n",
    "创建数据表使用 \"CREATE TABLE\" 语句，创建数据表前，需要确保数据库已存在，以下创建一个名为 sites 的数据表：\n",
    "\"\"\"\n",
    "import mysql.connector\n",
    "\n",
    "mydb = mysql.connector.connect(host=\"localhost\", username=\"root\", password=\"\", database=\"db_test\")\n",
    "\n",
    "cursor = mydb.cursor()\n",
    "\n",
    "cursor.execute(r\"CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))\")\n",
    "\n",
    "# 我们也可以使用 \"SHOW TABLES\" 语句来查看数据表是否已存在：\n",
    "# demo_mysql_test.py:\n",
    "# import mysql.connector\n",
    "#\n",
    "# mydb = mysql.connector.connect(\n",
    "#   host=\"localhost\",\n",
    "#   user=\"root\",\n",
    "#   passwd=\"123456\",\n",
    "#   database=\"db_test\"\n",
    "# )\n",
    "# mycursor = mydb.cursor()\n",
    "#\n",
    "# mycursor.execute(\"SHOW TABLES\")\n",
    "#\n",
    "# for x in mycursor:\n",
    "#   print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d476a37",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "删除表\n",
    "删除表使用 \"DROP TABLE\" 语句， IF EXISTS 关键字是用于判断表是否存在，只有在存在的情况才删除：\n",
    "\"\"\"\n",
    "\n",
    "import mysql.connector\n",
    "\n",
    "mydb = mysql.connector.connect(\n",
    "    host=\"localhost\",\n",
    "    user=\"root\",\n",
    "    passwd=\"\",\n",
    "    database=\"db_test\"\n",
    ")\n",
    "mycursor = mydb.cursor()\n",
    "\n",
    "sql = \"DROP TABLE IF EXISTS sites\"  # 删除数据表 sites\n",
    "\n",
    "mycursor.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06656dbb",
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "删除数据表中的记录\n",
    "''''\n",
    "import mysql.connector\n",
    "\n",
    "mydb = mysql.connector.connect(host=\"localhost\", username=\"root\", password=\"\", db=\"db_test\")\n",
    "\n",
    "cursor = mydb.cursor()\n",
    "\n",
    "sql = \"DELETE FROM sites WHERE name = 'stackoverflow'\"\n",
    "\n",
    "cursor.execute(sql)\n",
    "mydb.commit()\n",
    "\n",
    "print(cursor.rowcount, \" 条记录删除\")\n",
    "\n",
    "\n",
    "print(\"-----------------------------------------------\")\n",
    "# 注意：要慎重使用删除语句，删除语句要确保指定了 WHERE 条件语句，否则会导致整表数据被删除。\n",
    "#\n",
    "# 为了防止数据库查询发生 SQL 注入的攻击，我们可以使用 %s 占位符来转义删除语句的条件：\n",
    "cursor = mydb.cursor()\n",
    "\n",
    "sql = \"DELETE FROM sites WHERE name = %s\"\n",
    "na = (\"stackoverflow\",)\n",
    "\n",
    "cursor.execute(sql, na)\n",
    "\n",
    "mydb.commit()\n",
    "\n",
    "print(cursor.rowcount, \" 条记录删除\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b744dc9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "执行sql语句查询后,MySQLCursorBuffered游标标从服务器获取整个结果集并将他们放在缓冲区中。\n",
    "Buffered游标适用于多个小结果集的查询,且多个结果集之间的数据需要一起使用。\n",
    "使用buffered游标执行查询语句时 ,取行方法（如fetchone()，fechcall()等）返回的是缓冲区中的行。\n",
    "nonbuffered游标不从服务器获取数据,直到调用了某个获取数据行的方法, 在使用nonbuffered游标时,\n",
    "必须确保取出的结果是结果集中的所有行，才能再用同一连接执行其他语句,\n",
    "否则会报错InternalError(Unread result found)。\n",
    "解决方法：\n",
    "1.创建buffered游标，设置buffered 参数为\"True\" -->实例：Line 23\n",
    "2.多执行一次 fetchall() 操作将结果集中的所有行全部取出 -->实例： Line4 0\n",
    "\"\"\"\n",
    "\n",
    "import mysql.connector\n",
    "\n",
    "mydb = mysql.connector.connect(\n",
    "    host=\"localhost\",\n",
    "    user=\"root\",\n",
    "    passwd=\"\",\n",
    "    database=\"db_test\",\n",
    "    # buffered=True\n",
    ")\n",
    "cursor = mydb.cursor()\n",
    "\n",
    "\n",
    "print(\"----------------------------------------------\")\n",
    "# 如果我们只想读取一条数据，可以使用 fetchone() 方法：\n",
    "cursor.execute(\"SELECT * FROM sites\")\n",
    "\n",
    "result = cursor.fetchone()\n",
    "\n",
    "print(result)\n",
    "\n",
    "\n",
    "print(\"----------------------------------------------\")\n",
    "# 此处需执行 fetchall() 操作，否则程序执行时报了一个unread result found的异常\n",
    "# 不管针对数据库的查询有没有返回结果，必须要进行fetchxxx()\n",
    "# cursor.fetchall()\n",
    "\n",
    "# where 条件语句\n",
    "# 如果我们要读取指定条件的数据，可以使用 where 语句：\n",
    "sql = r\"SELECT * FROM sites WHERE name ='RUNOOB'\"\n",
    "\n",
    "cursor.execute(sql)\n",
    "\n",
    "result = cursor.fetchall()\n",
    "\n",
    "for x in result:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b416229",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "插入数据\n",
    "插入数据使用 \"INSERT INTO\" 语句：\n",
    "\"\"\"\n",
    "\n",
    "import mysql.connector\n",
    "\n",
    "mydb = mysql.connector.connect(host=\"localhost\", username=\"root\", password=\"\", database=\"db_test\")\n",
    "\n",
    "cursor = mydb.cursor()\n",
    "\n",
    "sql = \"INSERT INTO sites (name, url) VALUES (%s, %s)\"\n",
    "val = (\"RUNOOB\", \"https://www.runoob.com\")\n",
    "cursor.execute(sql, val)\n",
    "\n",
    "mydb.commit()  # 数据表内容有更新，必须使用到该语句\n",
    "\n",
    "print(cursor.rowcount, \"记录插入成功。\")\n",
    "\n",
    "\n",
    "# 批量插入\n",
    "# 批量插入使用 executemany() 方法，该方法的第二个参数是一个元组列表，包含了我们要插入的数据：\n",
    "sql = \"INSERT INTO sites (name, url) VALUES (%s, %s)\"\n",
    "val = [\n",
    "    ('Google', 'https://www.google.com'),\n",
    "    ('Github', 'https://www.github.com'),\n",
    "    ('Taobao', 'https://www.taobao.com'),\n",
    "    ('stackoverflow', 'https://www.stackoverflow.com/')\n",
    "]\n",
    "\n",
    "cursor.executemany(sql, val)\n",
    "\n",
    "mydb.commit()  # 数据表内容有更新，必须使用到该语句\n",
    "\n",
    "print(cursor.rowcount, \"记录插入成功。\")\n",
    "\n",
    "\n",
    "# 如果我们想在数据记录插入后，获取该记录的 ID ，可以使用以下代码：\n",
    "sql = \"INSERT INTO sites (name, url) VALUES (%s, %s)\"\n",
    "val = (\"Zhihu\", \"https://www.zhihu.com\")\n",
    "cursor.execute(sql, val)\n",
    "\n",
    "mydb.commit()\n",
    "\n",
    "print(\"1 条记录已插入, ID:\", cursor.lastrowid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11ce1d29",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "查询数据\n",
    "查询数据使用 SELECT 语句：\n",
    "\"\"\"\n",
    "\n",
    "import mysql.connector\n",
    "\n",
    "mydb = mysql.connector.connect(\n",
    "    host=\"localhost\",\n",
    "    user=\"root\",\n",
    "    passwd=\"\",\n",
    "    database=\"db_test\"\n",
    ")\n",
    "cursor = mydb.cursor()\n",
    "\n",
    "\n",
    "print(\"----------------------------------------------\")\n",
    "cursor.execute(\"SELECT * FROM sites\")\n",
    "\n",
    "result = cursor.fetchall()  # fetchall() 获取所有记录\n",
    "\n",
    "for x in result:\n",
    "    print(x)\n",
    "\n",
    "\n",
    "print(\"----------------------------------------------\")\n",
    "# 也可以读取指定的字段数据(主要根据SQL语句来调整\n",
    "# )：\n",
    "cursor.execute(\"SELECT name, url FROM sites\")\n",
    "\n",
    "result = cursor.fetchall()\n",
    "\n",
    "for x in result:\n",
    "    print(x)\n",
    "\n",
    "\n",
    "print(\"----------------------------------------------\")\n",
    "# 如果我们只想读取一条数据，可以使用 fetchone() 方法：\n",
    "cursor.execute(\"SELECT * FROM sites\")\n",
    "\n",
    "result = cursor.fetchone()\n",
    "\n",
    "print(result)\n",
    "\n",
    "\n",
    "print(\"----------------------------------------------\")\n",
    "# 此处需执行 fetchall() 操作，否则程序执行时报了一个unread result found的异常\n",
    "# 不管针对数据库的查询有没有返回结果，必须要进行fetchxxx()，\n",
    "# 否则进行下一个其他的insert、create等待操作的时候就会报unread result found的异常了。\n",
    "cursor.fetchall()\n",
    "\n",
    "# where 条件语句\n",
    "# 如果我们要读取指定条件的数据，可以使用 where 语句：\n",
    "sql = r\"SELECT * FROM sites WHERE name ='RUNOOB'\"\n",
    "\n",
    "cursor.execute(sql)\n",
    "\n",
    "result = cursor.fetchall()\n",
    "\n",
    "for x in result:\n",
    "    print(x)\n",
    "\n",
    "\n",
    "print(\"----------------------------------------------\")\n",
    "# 也可以使用通配符 %：\n",
    "sql = \"SELECT * FROM sites WHERE url LIKE '%oo%'\"\n",
    "\n",
    "cursor.execute(sql)\n",
    "\n",
    "result = cursor.fetchall()\n",
    "\n",
    "for x in result:\n",
    "    print(x)\n",
    "\n",
    "\n",
    "print(\"----------------------------------------------\")\n",
    "# 为了防止数据库查询发生 SQL 注入的攻击，我们可以使用 %s 占位符来转义查询的条件：\n",
    "sql = \"SELECT * FROM sites WHERE name = %s\"\n",
    "na = (\"RUNOOB\",)\n",
    "\n",
    "cursor.execute(sql, na)\n",
    "\n",
    "result = cursor.fetchall()\n",
    "\n",
    "for x in result:\n",
    "    print(x)\n",
    "\n",
    "\n",
    "print(\"----------------------------------------------\")\n",
    "# 排序\n",
    "# 查询结果排序可以使用 ORDER BY 语句，默认的排序方式为升序，关键字为 ASC，如果要设置降序排序，可以设置关键字 DESC。\n",
    "sql = \"SELECT * FROM sites ORDER BY name\"\n",
    "\n",
    "cursor.execute(sql)\n",
    "\n",
    "result = cursor.fetchall()\n",
    "\n",
    "for x in result:\n",
    "    print(x)\n",
    "\n",
    "\n",
    "print(\"----------------------------------------------\")\n",
    "# 降序排序实例：\n",
    "sql = \"SELECT * FROM sites ORDER BY name DESC\"\n",
    "\n",
    "cursor.execute(sql)\n",
    "\n",
    "result = cursor.fetchall()\n",
    "\n",
    "for x in result:\n",
    "    print(x)\n",
    "\n",
    "\n",
    "print(\"----------------------------------------------\")\n",
    "# Limit\n",
    "# 如果我们要设置查询的数据量，可以通过 \"LIMIT\" 语句来指定\n",
    "cursor.execute(\"SELECT * FROM sites LIMIT 5\")\n",
    "\n",
    "result = cursor.fetchall()\n",
    "\n",
    "for x in result:\n",
    "    print(x)\n",
    "\n",
    "\n",
    "print(\"----------------------------------------------\")\n",
    "# 也可以指定起始位置，使用的关键字是 OFFSET：\n",
    "cursor.execute(\"SELECT * FROM sites LIMIT 3 OFFSET 1\")  # 0 为 第一条，1 为第二条，以此类推\n",
    "\n",
    "result = cursor.fetchall()\n",
    "\n",
    "for x in result:\n",
    "    print(x)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
